﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class BusinessSystem : Basic, IBusinessSystem
    {
        private const string sqlGetSiteIDsContainsTheBusinessSystem = "select b.siteid,b.id from businesssystem b where name=:name";
        private const string sqlGetAllBusinessSystem = "select * from businesssystem";
        private const string selectGetDeviceByID = "select * from businesssystem where id=:id";
        private const string sqlDelete = "delete from businesssystem where id=:id";
        private const string sqlGetExistTheBSSites = "select s.* from businesssystem b inner join sitebranch s on b.siteid=s.id where b.name=:bsName";
        private const string sqlGetSiteAlertCountByBSID = "SELECT d.TYPE, d.ID,s.id siteid,s.alias sitename, (SELECT COUNT(id) FROM alert a WHERE a.deviceid=d.id AND  a.status='OPEN') AlertCount FROM device d " +
" INNER JOIN sitebranch s ON s.ID = d.siteid WHERE d.businesssystemid=:businesssystemid";
        public Wicresoft.UnifyShow.Business.BusinessSystemModel GetBusinessSystemByID(int id)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=id,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            return ModelHelper<BusinessSystemModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }


        public BusinessSystemModel GetBusinessSystemBySiteIdAndCName(int siteID, string serviceName)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="SiteID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=siteID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                        new QueryParameter(){ OracleDbColumnName="Name", OracleDbColumnType=OracleDbType.NVarchar2,OracleDbColumnSize=20,
                        OracleDbColumnValue=serviceName,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            return ModelHelper<BusinessSystemModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();

        }


        public List<BusinessSystemModel> GetSiteIDsContainsTheBusinessSystem(string serviceName)
        {
            return ModelHelper<BusinessSystemModel>.GetModel_By_DirectSql(sqlGetSiteIDsContainsTheBusinessSystem, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="name",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=20,Value=serviceName}
                 },
                 oda,
                 new List<string>() { "SiteID", "ID" });
        }

        public List<BusinessSystemModel> GetBusinessSystemBySiteIdAndCategory(int siteID, string categoryName)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="SiteID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=siteID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                        new QueryParameter(){ OracleDbColumnName="Category", OracleDbColumnType=OracleDbType.NVarchar2,OracleDbColumnSize=20,
                        OracleDbColumnValue=categoryName,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            return ModelHelper<BusinessSystemModel>.GetModel_By_QueryParams(attParam, null, null, null, oda);

        }

        public List<BusinessSystemAndAlertModel> GetBusinessAlertBySiteIdAndCategory(int siteID, string categoryName)
        {
            string sqlStr = string.Empty;
            sqlStr = string.Concat("select  b1.*,b2.severity from businesssystem b1 left join ",
                " (select t1.id id,max(t3.severity) severity from businesssystem t1, device t2, alert t3 where t1.id = t2.businesssystemid and t2.id = t3.deviceid  and t3.status='OPEN' group by t1.id) b2 ",
                " on b1.id = b2.id where b1.siteid = :siteID and b1.category=:categoryName ");
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            cmdParms.Add(new OracleParameter("siteID", OracleDbType.Int32, siteID, ParameterDirection.Input));
            cmdParms.Add(new OracleParameter("categoryName", OracleDbType.NVarchar2, 20, categoryName, ParameterDirection.Input));
            OracleDataReader odr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<BusinessSystemAndAlertModel>.GetModel_By_OracleDataReader(odr);
        }

        public List<BusinessSystemModel> GetAllBusinessSystem()
        {
            return ModelHelper<BusinessSystemModel>.GetModel_By_DirectSql(sqlGetAllBusinessSystem, null, oda);
        }


        public int Add(BusinessSystemModel model)
        {
            return ModelHelper<BusinessSystemModel>.AddModel_By_QueryParams(model, new List<string>() { "ID" }, oda);
        }

        public void Delete(decimal id)
        {
            ModelHelper<BusinessSystemModel>.UpdateOrDeleteModel_By_DirectSql(sqlDelete, new List<OracleParameter>()
            {
                new OracleParameter(){ ParameterName="id",OracleDbType=OracleDbType.Decimal,Value=id},
            }, oda);
        }

        public int Update(BusinessSystemModel model)
        {
            return ModelHelper<BusinessSystemModel>.UpdateModel_By_QueryParams(new List<QueryParameter>(){
                new QueryParameter(){OracleDbColumnName="ID",OracleDbColumnValue=model.ID,OracleDbColumnType=OracleDbType.Decimal,
                    PredicateType=PredicateType.where,QueryOperator=QueryOperatorType.equal}
            }, model, new List<string>() { "ID" }, oda);
        }


        public int AddList(IList<BusinessSystemModel> models)
        {
            return ModelHelper<BusinessSystemModel>.AddListModel_By_QueryParams(models, new List<string>() { "ID" }, oda);
        }


        public List<SiteBranchModel> GetExistTheBSSites(string bsName)
        {
            return ModelHelper<SiteBranchModel>.GetModel_By_DirectSql(sqlGetExistTheBSSites, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="bsName",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=20,Value=bsName}
                 },
          oda);
        }


        public IList<DeviceAndAlertCountModel> GetSiteAlertCountByBSID(int bsID)
        {
            return ModelHelper<DeviceAndAlertCountModel>.GetModel_By_DirectSql(sqlGetSiteAlertCountByBSID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="businesssystemid",OracleDbType=OracleDbType.Decimal,/*字符串类型必须有Size*/Size=20,Value=bsID}
                 },
     oda);
        }
    }
}
